package com.huishi.util;

import com.huishi.entity.DataSource;
import com.huishi.enums.DBType;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.LinkedList;
import java.util.Map;
import java.util.Queue;

/**
 * 数据库工具类
 */
public class DBUtils {
    private static Logger log = LoggerFactory.getLogger(DBUtils.class);

    /**
     * 测试数据库连接是否通的
     *
     * @param dbType
     * @param ip
     * @param port
     * @param username
     * @param password
     * @return
     */
    public static String testDBConnected(String dbType, String ip, String port, String username, String password) {
        log.info("数据库连接测试，ip:{},port:{},username:{},password:{}", ip, port, username, password);

        String testMsg = "";
        if ("mysql".equals(dbType.toLowerCase())) {
            String url = "jdbc:mysql://" + ip + ":" + port + "";
            Connection connection = null;

            try {
                // 加载MySQL驱动
                Class.forName("com.mysql.cj.jdbc.Driver");
                // 获取数据库连接
                connection = DriverManager.getConnection(url, username, password);
                // 如果连接成功，则打印连接成功消息
                if (connection != null) {
                    testMsg = "数据库连接成功！";
                }
            } catch (ClassNotFoundException e) {
                // 如果驱动类没有找到，则打印错误消息
                Throwable lastException = getLastThrowable(e);
                testMsg = lastException.getMessage();
                log.error("找不到MySQL驱动类", e);
            } catch (SQLException e) {
                Throwable lastException = getLastThrowable(e);
                testMsg = lastException.getMessage();
                log.error(e.getMessage());
            } finally {
                // 最后关闭数据库连接
                if (connection != null) {
                    try {
                        connection.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
        }


        return testMsg;
    }


//    public static void syncData(String sourceUrl, String sourceUser, String sourcePassword,
//                                String targetUrl, String targetUser, String targetPassword,
//                                List<String> tables, ProgressDialog progressDialog) {
//
//    }

    /**
     * 创建数据库表结构到目标数据源
     *
     * @param sourceConn 源数据库连接
     * @param targetConn 目标数据库连接
     * @param tableName  表名
     * @throws SQLException
     */
    public static void createTableToTarget(Connection sourceConn, Connection targetConn, String tableName) throws SQLException {
        Statement sourceStmt = sourceConn.createStatement();
        // 获取创建表的sql
        ResultSet resultSet = sourceStmt.executeQuery("SHOW CREATE TABLE " + tableName);
        if (resultSet.next()) {
            String createTableSQL = resultSet.getString(2);
            Statement targetStmt = targetConn.createStatement();
            targetStmt.executeUpdate("DROP TABLE IF EXISTS " + tableName);
            targetStmt.executeUpdate(createTableSQL);
        }
    }

    /**
     * 获取数据库连接
     *
     * @param dataSource
     * @param dbName
     * @return
     * @throws SQLException
     */
    public static Connection genDBConn(DataSource dataSource, String dbName) throws SQLException {
        String sourceUrl = null;
        if (DBType.MYSQL.name().equalsIgnoreCase(dataSource.getSourceType()))
            sourceUrl = "jdbc:mysql://" + dataSource.getIp() + ":" + dataSource.getPort() + "/" + dbName +
                    "?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8";

        return DriverManager.getConnection(sourceUrl, dataSource.getUserName(), dataSource.getPassword());
    }

    /**
     * 异常链，获取最后一个异常
     *
     * @param e
     * @return
     */
    public static Throwable getLastThrowable(Throwable e) {
        Throwable current = e;
        while (current.getCause() != null) {
            current = current.getCause();
        }
        return current;
    }

    /**
     * 备份数据库
     *
     * @param host
     * @param port
     * @param user
     * @param password
     * @param dbName
     * @param tableName
     * @param backupFile
     * @return
     */
    public static boolean backupDatabase(String host, String port, String user, String password,
                                         String dbName, String tableName, String backupFile) {

        String command = String.format("mysqldump -h %s -P %s -u %s -p%s %s %s > %s", host, port, user, password, dbName, tableName, backupFile);
        return (boolean) CMDUtils.executive(command).get("result");
    }

    /**
     * 按批次备份表数据（不导出表结构，仅数据）
     *
     * @param host
     * @param port
     * @param user
     * @param password
     * @param dbName
     * @param tableName
     * @param offset
     * @param size
     * @param backupFile
     * @return
     */
    public static boolean backupDataBatch(String host, String port, String user, String password,
                                          String dbName, String tableName, int offset, int size,
                                          String backupFile) {

        String command = String.format("mysqldump -h %s -P %s -u %s -p%s %s %s --where=\"1 LIMIT %s,%s\" --no-create-info > %s",
                host, port, user, password, dbName, tableName, offset, size, backupFile);
        return (boolean) CMDUtils.executive(command).get("result");
    }

    /**
     * 备份表结构
     *
     * @param host
     * @param port
     * @param user
     * @param password
     * @param dbName
     * @param tableName
     * @param backupFile
     * @return
     */
    public static boolean backupTableStructure(String host, String port, String user, String password,
                                               String dbName, String tableName, String backupFile) {

        String command = String.format("mysqldump -h %s -P %s -u %s -p%s --no-data %s %s > %s",
                host, port, user, password, dbName, tableName, backupFile);
        return (boolean) CMDUtils.executive(command).get("result");
    }

    /**
     * 恢复数据库
     *
     * @param host
     * @param port
     * @param user
     * @param password
     * @param dbName
     * @param backupFile
     * @return
     */
    public static boolean restoreDatabase(String host, String port, String user, String password,
                                          String dbName, String backupFile) {
        String command = String.format("mysql -h %s -P %s -u %s -p%s %s < %s", host, port, user, password, dbName, backupFile);
        return ((boolean) CMDUtils.executive(command).get("result"));
    }

    public static int getTotalRow(String host, String port, String user, String password,
                                  String dbName, String tableName) {
        String command = String.format("mysql -h %s -P %s -u %s -p%s %s -N -e \"select count(*) from %s\"", host, port, user, password, dbName, tableName);
        Map<String, Object> executive = CMDUtils.executive(command);
        return ((boolean) executive.get("result")) ? Integer.parseInt(String.valueOf(executive.get("value"))) : 0;

    }

    /**
     * 执行sql
     * @param host
     * @param port
     * @param user
     * @param password
     * @param dbName
     * @param sqlFile 要执行的sql文件
     * @return
     */
    public static boolean executeSQL(String host, String port, String user, String password,
                                     String dbName, String sqlFile) {

        String command = String.format("mysql -h %s -P %s -u %s -p%s %s < %s",
                host, port, user, password, dbName, sqlFile);
        return ((boolean) CMDUtils.executive(command).get("result"));
    }

    public static Map<String, Object> executeSQLByCmd(String host, String port, String user, String password,
                                                      String dbName, String sql) {

        String command = String.format("mysql -h %s -P %s -u %s -p%s -D %s -e \"%s;\"",
                host, port, user, password, dbName, sql);
        return CMDUtils.executive(command);
    }

    public static void main(String[] args) {
        Queue<Integer> queue = new LinkedList<>();// 先进先出队列
        queue.add(1);
        queue.add(2);
        queue.add(3);
        System.out.println(queue);
        System.out.println(queue.poll());
        System.out.println(queue);
    }
}
